const xlsx = require('xlsx');
const node_xlsx = require('node-xlsx');
const fs = require('fs');
const _ = require('lodash');
var iconv = require('iconv-lite');

let filePath = './000000.xlsx';

// let result = xlsx.parse(filePath);

let rs = fs.createReadStream(filePath);

process_RS(rs, wb => {
    let xlsData = [];
    for(let si = 0; si < 3; si++){
        let first_sheet_name = wb.SheetNames[si];
        let ws = wb.Sheets[first_sheet_name];

        let result = {};
        let typeMap = {};

        for (let m in ws) {
            /* all keys that do not begin with "!" correspond to cell addresses */
            if (m[0] === '!') continue;
            let value = ws[m].w;

            // let values = value.split(/;+/);
            //
            let type = m.match(/([A-Z])(.*)/);
            if(!type) continue;
            if(type[2] == 1){
                typeMap[type[1]] = value;
            }else{
                type = type[1];

                // for (let v of values) {
                //         if (v) {
                if(!result[type]){
                    result[type] = [];
                }
                result[type].push(value);
                //             if (!result[type][v]) {
                //                 result[type][v] = {
                //                     keyword: v,
                //                     count: 0
                //                 }
                //             }
                //             result[type][v].count++;
                //         }
                //     }
            }
            // checkIds.push(ws[m].w);
        }
        let common = _.intersection(result.A, result.B,result.C,result.D,result.E,result.F);
        common = _.map(common, i => {return {'iccid':i}});

        console.log(common);

        // for(let type in typeMap){
        //     let tmp = result[type];
        //     let re = _.map(tmp, (v, k) => v);
        //     let orderedR = _.orderBy(re, ['count'],['desc']);
        //
        xlsData.push({
            sheet : first_sheet_name,
            title : [{
                key : 'iccid',
                name : "ICCID"
            }],
            data : common
        });
    }

    let buffer = buildExcelResultBuf(xlsData);
    fs.writeFileSync( '保留重复.xlsx', buffer);
    // console.log('文档以输出，请查阅。');
});

function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
    var buffers = [];
    stream.on('data', function(data) { buffers.push(data); });
    stream.on('end', function() {
        var buffer = Buffer.concat(buffers);
        // buffer = iconv.decode(buffer,'gbk');
        var workbook = xlsx.read(buffer, {type:"buffer"});

        /* DO SOMETHING WITH workbook IN THE CALLBACK */
        cb(workbook);
    });
}

function xlsxParser(file, handler) {
    var result = {code: 1, msg: ''};

    if (!file.originalname.endsWith('xlsx') && !file.originalname.endsWith('xls')) {
        result.code = 0;
        result.msg = '上传文件格式不正确，请上传excel文件';
        return result;
    }
    var arr = [];
    for (var i = 0; i != file.buffer.length; ++i) {
        arr[i] = String.fromCharCode(file.buffer[i]);
    }
    var bstr = arr.join('');

    var wb = XLSX.read(bstr, {type: 'binary'});

    var first_sheet_name = wb.SheetNames[0];

    /* Get worksheet */
    var ws = wb.Sheets[first_sheet_name];
    return handler(ws);
}

/**
 * 构建xlsx内容，opts中每一项为{sheet：String, title: [], data: []}
 * @param opts = Array
 * @returns Buffer
 */
function buildExcelResultBuf(opts){
    let finData = [];
    for (let sheet of opts) {
        let data = [];
        let title = sheet.title.map(function (item) {
            return item.name;
        });
        data.push(title);

        for (let key in sheet.data) {
            let row = [];
            _.each(sheet.title, function(item){
                row.push(sheet.data[key][item.key]);
            });
            data.push(row);
        }
        finData.push({name : sheet.sheet, data : data});
    }

    let buffer = node_xlsx.build(finData);
    return buffer;
}